# coding:utf-8
import pymysql
import pandas as pd

# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="hui123456", db='dbtest')
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
sql = """
      SELECT order2,
             product_BianMa
      FROM doudian_day
      WHERE
          MONTH (day) = 10
        AND (
          order_zt = "已签收"
         OR order_zt = "待发货"
         OR order_zt = "已完成"
         OR order_zt = "已发货"
         OR order_zt = "已支付"
          )
        AND order_sh NOT IN (
          "退款成功"
          , "售后完成"
          , "退款完成"
          , "已全额退款"
          , "退货退款完成"
          ) \
      """

cursor.execute(sql)
result = cursor.fetchall()
dou = pd.DataFrame(list(result))

dou.columns = ['订单id', '商家编码']

# 佣金
y1 = pd.read_excel(r'G:\工作\2025年订单\10月\佣金1.xlsx',
                   dtype={'订单id': str, '商品id': str, '作者账号': str, '抖音/火山号': str})
y2 = pd.read_excel(r'G:\工作\2025年订单\10月\佣金2.xlsx',
                   dtype={'订单id': str, '商品id': str, '作者账号': str, '抖音/火山号': str})
y3 = pd.read_excel(r'G:\工作\2025年订单\10月\佣金3.xlsx',
                   dtype={'订单id': str, '商品id': str, '作者账号': str, '抖音/火山号': str})
y4 = pd.read_excel(r'G:\工作\2025年订单\10月\佣金4.xlsx',
                   dtype={'订单id': str, '商品id': str, '作者账号': str, '抖音/火山号': str})
y = pd.concat([y1, y2, y3, y4])
y['订单id'] = y['订单id'].str.strip()
y1 = y[y['订单状态'] != '订单退货退款']

# 团长
z1 = pd.read_excel(r'G:\工作\2025年订单\10月\招商1.xlsx', dtype={'订单id': str})
z2 = pd.read_excel(r'G:\工作\2025年订单\10月\招商2.xlsx', dtype={'订单id': str})
z3 = pd.read_excel(r'G:\工作\2025年订单\10月\招商3.xlsx', dtype={'订单id': str})
z4 = pd.read_excel(r'G:\工作\2025年订单\10月\招商4.xlsx', dtype={'订单id': str})
z = pd.concat([z1, z2, z3, z4])
z['订单id'] = z['订单id'].str.strip()
z_date = z.loc[:, ['订单id', '服务费率', '出单机构']]

# 匹配商家编码
result_1 = pd.merge(y1, dou, on='订单id', how='left')
# 匹配招商团长
result_2 = pd.merge(result_1, z_date, on='订单id', how='left')

result_2.to_excel(r'G:\工作\2025年订单\10月\绩效\10月招商提成明细.xlsx')
